

# 产品表
class Product():
    create_command = '''
                CREATE TABLE IF NOT EXISTS products (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT NOT NULL,
                    image_folder TEXT NOT NULL,
                    description TEXT,
                    price REAL NOT NULL,
                    seller_id INTEGER NOT NULL,
                    is_completed BOOLEAN DEFAULT 0,
                    buyer_id INTEGER DEFAULT -1,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    completed_at TIMESTAMP DEFAULT NULL,
                    seller_last_view_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (seller_id) REFERENCES users(id),
                    FOREIGN KEY (buyer_id) REFERENCES users(id)
                )
            '''

# 用户表
class User():
    create_command = '''
                CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    nickname TEXT NOT NULL,
                    description TEXT,
                    avatar TEXT,
                    email TEXT UNIQUE NOT NULL,
                    password TEXT NOT NULL,
                    address TEXT DEFAULT '暂无常用地址',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            '''

# 验证码信息表
class VerificationCode():
    create_command = '''
                CREATE TABLE IF NOT EXISTS verification_codes (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    email TEXT NOT NULL,
                    code TEXT NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    expired_at TIMESTAMP NOT NULL,
                    FOREIGN KEY (email) REFERENCES users(email)
                )
            '''

# 商品评论表
class ProductComment():
    create_command = '''
                CREATE TABLE IF NOT EXISTS product_comments (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    product_id INTEGER NOT NULL,
                    user_id INTEGER NOT NULL,
                    parent_comment_id INTEGER DEFAULT -1,
                    content TEXT NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (product_id) REFERENCES products(id),
                    FOREIGN KEY (user_id) REFERENCES users(id),
                    FOREIGN KEY (parent_comment_id) REFERENCES product_comments(id)
                )
            '''

# 关注列表表
class FollowList():
    create_command = '''
                CREATE TABLE IF NOT EXISTS favorites (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id INTEGER NOT NULL,
                    product_id INTEGER NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    last_view_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (user_id) REFERENCES users(id),
                    FOREIGN KEY (product_id) REFERENCES products(id),
                    UNIQUE(user_id, product_id)
                )
            '''

# 商品图片信息记录表
class ProductImage():
    create_command = '''
                CREATE TABLE IF NOT EXISTS product_images (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    product_id INTEGER NOT NULL,
                    image_path TEXT NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (product_id) REFERENCES products(id)
                )
            '''


